package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.domain.Product;
import pos.utils.UIDGenerator;

public class JDBCProductDAO implements IProductDAO {
	
	public void deleteProduct(Connection conn, String productID) {
		String sql = "DELETE FROM Products WHERE (OID = ?)";
        PreparedStatement stmt = null;
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, productID);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}
         
    public String selectOIDOfProduct(Connection conn, String productid) {
		PreparedStatement stmt = null;
		String sql = "SELECT FROM Products WHERE (productID = ?)";
		String oid = null;
		ResultSet result = null;
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, productid);
			result = stmt.executeQuery();
			
			result.next();
			oid = result.getString("oid");
			
		} catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return oid;
	}
	

	public void insertProduct(Connection conn, Product producto) {
		String oido = UIDGenerator.getInstance().getKey();
        PreparedStatement stmt = null;
        String sql = "INSERT INTO Products (oid, productID, productCatID, productorID, name, description) VALUES (?, ?, ?, ?, ?, ?)";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, oido);
            stmt.setString(2, producto.getProductID());
            stmt.setString(3, producto.getProductorID());
            stmt.setString(4, producto.getProductCatID());
            stmt.setString(5, producto.getName());
            stmt.setString(6, producto.getDescription());

            stmt.executeUpdate();
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
	

	public Product selectProduct(Connection conn, String productID) {
		PreparedStatement stmt = null;
        ResultSet result = null;
        Product p = null;
        String sql = "SELECT * FROM products WHERE (productID = ?) ";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, productID);
            result = stmt.executeQuery();

            result.next();
            p = new Product();
            p.setProductID(result.getString("productID"));
            p.setProductorID(result.getString("productorID"));
            p.setDescription(result.getString("description"));
            p.setName(result.getString("name"));
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return p;
	}
	
	public List<Product> selectAllProducts() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List<Product> searchResults = new LinkedList<Product>();
		ResultSet result = null;

		try {
			String sql = "SELECT * FROM Products";
			stmt = conn.prepareStatement(sql);
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				Product temp = new Product();
				temp.setProductID(result.getString("productID"));
				temp.setProductCatID(result.getString("productCatID"));
				temp.setDescription(result.getString("description"));
				temp.setProductorID(result.getString("productorID"));
				temp.setName(result.getString("name"));
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return searchResults;
	}


	public List<Product> selectAllProductsFromCategory(Connection conn, String productCatID) {
        PreparedStatement stmt = null;
        List<Product> searchResults = new LinkedList<Product>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Products WHERE (productCatID = ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, productCatID);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Product temp = new Product();
                temp.setProductID(result.getString("productID"));
                temp.setProductCatID(result.getString("productCatID"));
                temp.setDescription(result.getString("description"));
                temp.setProductorID(result.getString("productorID"));
                temp.setName(result.getString("name"));
                searchResults.add(temp);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
        return searchResults;
	}

}
